﻿#region Using

using System;
using System.Collections.Generic;
using System.Collections;
using System.Collections.Specialized;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraBars;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraTab;
using System.Data;
using DevExpress.XtraTreeList;
using System.Data.SqlClient;
#endregion Using

namespace TCCT_CTC_CSDLDangVienV1
{
    class MyCommon
    {
        public static string CurrentUserID = "";
        public static string CurrentPassword = "";
        public static string CurrentUserHoTen = "";
        public static bool CurrentUserIsAdministrator = false;
        public static bool CurrentUserIsAdmin = false;
        public static string CurrentUserDauMoiID = "";

        /// <summary>
        /// Hàm định dạng số hiệu sĩ quan, ví dụ: 88.265.498
        /// </summary>
        /// <param name="inputSHSQ"></param>
        /// <returns></returns>
        public static string SHSQ_Parse(string inputSHSQ)
        {
            inputSHSQ = inputSHSQ.Replace(".", "");
            if (inputSHSQ.Length == 8)
                inputSHSQ = inputSHSQ.Substring(0, 2) + "." + inputSHSQ.Substring(2, 3) + "." + inputSHSQ.Substring(5);
            else if (inputSHSQ.Length >= 9)
                inputSHSQ = inputSHSQ.Substring(0, 3) + "." + inputSHSQ.Substring(3, 3) + "." + inputSHSQ.Substring(6);


            return inputSHSQ;
        }
        public static void FillDataUserToLookUp(LookUpEdit control)
        {
            string sql = "select *  " +
                           "From tblUser " +
                           "Order By HoTen ";

            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql);

            control.Properties.Columns.Add(new LookUpColumnInfo("ID"));
            control.Properties.Columns["ID"].Caption = "ID";
            control.Properties.Columns["ID"].Width = 50;

            control.Properties.Columns.Add(new LookUpColumnInfo("HoTen"));
            control.Properties.Columns["HoTen"].Caption = "Nhân viên";
            control.Properties.Columns["HoTen"].Width = 200;
            control.Properties.Columns.Add(new LookUpColumnInfo("Username"));
            control.Properties.Columns["Username"].Caption = "Username";
            control.Properties.Columns["Username"].Width = 250;
            control.Properties.Columns.Add(new LookUpColumnInfo("MoTa"));
            control.Properties.Columns["MoTa"].Caption = "Mô tả";
            control.Properties.Columns["MoTa"].Width = 100;
            control.Properties.PopupWidth = 250;
            control.Properties.DisplayMember = "HoTen";
            control.Properties.ValueMember = "ID";


            DataRow dr = temDT.NewRow();
            dr["HoTen"] = "- Lựa chọn -";
            dr["ID"] = DBNull.Value;
            temDT.Rows.Add(dr);

            temDT.DefaultView.Sort = "HoTen";

            temDT.AcceptChanges();

            control.Properties.DataSource = temDT.DefaultView;

            control.Properties.TextEditStyle = TextEditStyles.Standard;
        }
        /// <summary>
        /// Hàm đọc giá trị trong Datarow với tên cột truyền vào, nếu row mà ko có cột đó thì trả về null
        /// Hàm này dùng trong các đoạn code đồng bộ dữ liệu
        /// </summary>
        /// <param name="InputDR"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static object GetObj(DataRow InputDR, string fieldName)
        {
            try
            {
                return InputDR[fieldName];
            }
            catch
            {
                return null;
            }
        }
        public static void ShowDialogMessage(object _input)
        {
            MessageBox.Show(_input.ToString());

        }
        /// <summary>
        /// Hàm hiển thị thông báo trên form main sau 3 dây rồi tự ẩn
        /// </summary>
        /// <param name="_input"></param>
        public static void ShowMessage(object _input)
        {
            frmMain.Instance.txtMessage.Text = _input.ToString();
            frmMain.Instance.txtMessage.Visible = true;
            frmMain.Instance.txtMessage.DeselectAll();
            frmMain.Instance.timerMessage.Interval = 3000;
            frmMain.Instance.timerMessage.Enabled = true;
        }

        public static void ShowMessage(object _input, int _intervan)
        {
            frmMain.Instance.txtMessage.Text = _input.ToString();
            frmMain.Instance.txtMessage.Visible = true;
            frmMain.Instance.txtMessage.DeselectAll();
            frmMain.Instance.timerMessage.Interval = _intervan;
            frmMain.Instance.timerMessage.Enabled = true;
        }
        /// <summary>
        /// Hàm hiển thị thông báo trên form gọi nó, sau 3 giây rồi tự ẩn
        /// </summary>
        /// <param name="_input"></param>
        /// <param name="_Owner"></param>
        public static void ShowMessageOwner(object _input, DevExpress.XtraEditors.XtraForm _Owner)
        {
            frmMessageBox ff = new frmMessageBox();
            ff.memoEdit1.Text = _input.ToString();
            ff.Show(_Owner);
        }
        /// <summary>
        /// Hàm trả về khóa mới cho cán bộ thêm mới. tức là lấy CurentID trng bảng tblDau_Moi và tăng 1
        /// </summary>
        /// <param name="MyTran"></param>
        /// <param name="DauMoiID"></param>
        /// <returns></returns>
        public static int GetNewCanBoID(SqlTransaction MyTran, string DauMoiID)
        {
            SqlHelper.ExecuteNonQuery(MyTran, CommandType.Text, "Update tblDau_Moi Set CurentID=CurentID+1 Where ID='" + DauMoiID + "'");
            return Util.ToInt(SqlHelper.ExecuteScalar(MyTran, CommandType.Text, "Select CurentID From tblDau_Moi Where ID='" + DauMoiID + "'"));
        }
        /// <summary>
        /// Hàm trả về tên của từ điển
        /// </summary>
        /// <param name="MaDanhMuc"></param>
        /// <param name="TuDienID"></param>
        /// <param name="DauMoiID"></param>
        /// <returns></returns>
        public static string GetTuDien(string MaDanhMuc, object TuDienID, string DauMoiID)
        {
            if (TuDienID == null || TuDienID.ToString() == "") return "";
            string sql = "SELECT TenGoi From tblTuDien Where MaDanhMuc=@MaDanhMuc And TuDienID=@TuDienID And DauMoiID=@DauMoiID";

            List<SqlParameter> arrParameters = new List<SqlParameter>();

            arrParameters.Add(SqlHelper.CreateParameterObject("@MaDanhMuc", SqlDbType.NVarChar, ParameterDirection.Input, MaDanhMuc));
            arrParameters.Add(SqlHelper.CreateParameterObject("@TuDienID", SqlDbType.NVarChar, ParameterDirection.Input, TuDienID));
            arrParameters.Add(SqlHelper.CreateParameterObject("@DauMoiID", SqlDbType.NVarChar, ParameterDirection.Input, DauMoiID));
            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
            if (temDT.Rows.Count == 0)
                return "";
            else
                return temDT.Rows[0][0].ToString();
        }

        public static bool CheckExitsTuDien(string MaDanhMuc, object TuDienID, string DauMoiID)
        {
            if (TuDienID == null || TuDienID.ToString() == "") return false;
            string sql = "SELECT TenGoi From tblTuDien Where MaDanhMuc=@MaDanhMuc And TuDienID=@TuDienID And DauMoiID=@DauMoiID";

            List<SqlParameter> arrParameters = new List<SqlParameter>();

            arrParameters.Add(SqlHelper.CreateParameterObject("@MaDanhMuc", SqlDbType.NVarChar, ParameterDirection.Input, MaDanhMuc));
            arrParameters.Add(SqlHelper.CreateParameterObject("@TuDienID", SqlDbType.NVarChar, ParameterDirection.Input, TuDienID));
            arrParameters.Add(SqlHelper.CreateParameterObject("@DauMoiID", SqlDbType.NVarChar, ParameterDirection.Input, DauMoiID));
            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
            return temDT.Rows.Count > 0 ? true : false;

        }




        public static string GetTuDienID(string MaDanhMuc, string TenTuDien, string DauMoiID)
        {
            if (TenTuDien == null || TenTuDien.ToString() == "") return "";
            string sql = "SELECT TuDienID From tblTuDien Where MaDanhMuc=@MaDanhMuc And TenGoi=@TenGoi And DauMoiID=@DauMoiID";

            List<SqlParameter> arrParameters = new List<SqlParameter>();

            arrParameters.Add(SqlHelper.CreateParameterObject("@MaDanhMuc", SqlDbType.NVarChar, ParameterDirection.Input, MaDanhMuc));
            arrParameters.Add(SqlHelper.CreateParameterObject("@TenGoi", SqlDbType.NVarChar, ParameterDirection.Input, TenTuDien));
            arrParameters.Add(SqlHelper.CreateParameterObject("@DauMoiID", SqlDbType.NVarChar, ParameterDirection.Input, DauMoiID));
            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
            if (temDT.Rows.Count == 0)
                return "";
            else
                return temDT.Rows[0][0].ToString();
        }
        public static string GetTen_TuVietTat(string TuVietTat)
        {

            string sql = "SELECT ChuoiVB From tblVietTat Where VietTat=@VietTat ";

            List<SqlParameter> arrParameters = new List<SqlParameter>();

            arrParameters.Add(SqlHelper.CreateParameterObject("@VietTat", SqlDbType.NVarChar, ParameterDirection.Input, TuVietTat));
            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
            if (temDT.Rows.Count == 0)
                return "";
            else
                return temDT.Rows[0][0].ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="MaDanhMuc"></param>
        /// <param name="TuDienID"></param>
        /// <param name="DauMoiID"></param>
        /// <returns></returns>
        public static string GetTuDienCapTren(string MaDanhMuc, object TuDienID, string DauMoiID)
        {
            object myO = SqlHelper.ExecuteScalar(CommandType.Text, "Select CapTren From tblTuDien Where MaDanhMuc='" + MaDanhMuc + "' And TuDienID='" + TuDienID + "' And DauMoiID='" + DauMoiID + "' ");
            if (myO == null) return "";
            string[] arrCapTren = myO.ToString().Split(';');
            string temP = "";
            for (int i = 0; i < arrCapTren.Length; i++)
            {
                if (arrCapTren[i].Trim() != "")
                {
                    if (temP != "") temP += " - ";
                    temP += SqlHelper.ExecuteScalar(CommandType.Text, "Select TenGoi From tblTuDien Where MaDanhMuc='" + MaDanhMuc + "' And TuDienID='" + arrCapTren[i] + "' And DauMoiID='" + DauMoiID + "' ").ToString() + " ";
                }
            }
            return temP;
        }

        /// <summary>
        /// Hàm trả về tên của từ điển, chỉ dùng trong form từ điển
        /// </summary>
        /// <param name="ID">Vì trong bảng từ điển ID là duy nhất trong mọi trường hợp từ điển ở cấp nào</param>
        /// <returns></returns>
        public static string GetTuDien(string ID)
        {
            try
            {
                return SqlHelper.ExecuteScalar(CommandType.Text, "Select TenGoi From tblTuDien Where ID='" + ID + "'").ToString();
            }
            catch
            {
                return "";
            }

        }

        /// <summary>
        /// Hàm lấy tên đầu mối với ID đầu mối truyền vào.
        /// </summary>
        /// <param name="DauMoiID"></param>
        /// <returns></returns>
        public static string GetDauMoi(string DauMoiID)
        {
            try
            {
                return SqlHelper.ExecuteScalar(CommandType.Text, "Select TenDV From tblDau_Moi Where ID='" + DauMoiID + "' ").ToString();
            }
            catch
            {
                return "";
            }
        }
        public static string GetPhuongAnDeBat(string PhuongAnID, string DauMoiID)
        {
            try
            {
                return SqlHelper.ExecuteScalar(CommandType.Text, "Select TenGoi From tblDeBatPhuongan Where ID='" + PhuongAnID + "' And DauMoiID='" + DauMoiID + "' ").ToString();
            }
            catch
            {
                return "";
            }
        }


        /// <summary>
        /// Hàm mở form tìm kiếm từ điển và gán luôn giá trị  txtSender.Tag = ff.SelectedID; txtSender.Text = ff.SelectedTen;
        /// </summary>
        /// <param name="sender">Đối tượng ButtonEdit</param>
        /// <param name="sMa">Mã từ điển</param>
        /// <param name="sTen">Tên gọi danh mục từ điển</param>
        public static void MyTuDien(string DauMoiID, object sender, string sMa, string sTen)
        {
            try
            {
                DevExpress.XtraEditors.ButtonEdit txtSender = (DevExpress.XtraEditors.ButtonEdit)sender;

                DanhMuc.frmTuDien ff = new DanhMuc.frmTuDien(sMa, sTen);
                ff.SelectedDauMoiID = DauMoiID;
                ff.IsFormFind = true;
                if (ff.ShowDialog() == DialogResult.OK)
                {
                    txtSender.Tag = ff.SelectedID;
                    txtSender.Text = ff.SelectedTen;
                }
            }
            catch (Exception _ex)
            {
                MyCommon.ShowDialogMessage(_ex);
            }
        }
        /// <summary>
        /// Hàm mở Dialog từ điển và trả về tên của danh mục đã chọn, bao gồm tên của tất cả cấp trên của danh mục chọn
        /// Gán luôn giá trị  txtSender.Tag = ff.SelectedID; txtSender.Text = tất cả tên và cấp trên;
        /// </summary>
        /// <param name="DauMoiID"></param>
        /// <param name="sender"></param>
        /// <param name="sMa"></param>
        /// <param name="sTen"></param>
        public static void MyTuDienCapTren(string DauMoiID, object sender, string sMa, string sTen)
        {
            try
            {
                DevExpress.XtraEditors.ButtonEdit txtSender = (DevExpress.XtraEditors.ButtonEdit)sender;

                DanhMuc.frmTuDien ff = new DanhMuc.frmTuDien(sMa, sTen);
                ff.SelectedDauMoiID = DauMoiID;
                ff.IsFormFind = true;
                if (ff.ShowDialog() == DialogResult.OK)
                {

                    txtSender.Tag = ff.SelectedID;

                    DevExpress.XtraTreeList.Nodes.TreeListNode MyNode = ff.dgData.FocusedNode;
                    txtSender.Text = ff.SelectedTen;
                    while (MyNode.ParentNode != null)
                    {
                        MyNode = MyNode.ParentNode;
                        txtSender.Text = MyNode.GetValue("TenGoi") + " - " + txtSender.Text;
                    }

                }
            }
            catch (Exception _ex)
            {
                MyCommon.ShowDialogMessage(_ex);
            }
        }

        /// <summary>
        /// Hàm gán giá trị cho đối tượng mà tên control chùng với tên cột, với điều kiện tên control có tiền tố txtTêncột hoặc chkTêncột
        /// Không gán giá trị ID của từ điển vào control.Tag
        /// </summary>
        /// <param name="temDT">Bảng dữ liệu nguồn, dữ liệu sẽ được lấy từ dòng đầu tiên của bảng</param>
        /// <param name="control">Là TextEdit hoặc ButtonEdit, hoặc MemoEdit, hoặc CheckEdit, lookupEdit</param>
        public static void SetValue(DataTable temDT, object control)
        {
            if (control.GetType().ToString() == "DevExpress.XtraEditors.TextEdit")
            {
                TextEdit txtControl = (TextEdit)control;

                txtControl.Text = temDT.Rows[0][txtControl.Name.Replace("txt", "")].ToString();

            }
            else if (control.GetType().ToString() == "DevExpress.XtraEditors.ButtonEdit")
            {
                ButtonEdit txtControl = (ButtonEdit)control;
                txtControl.Text = temDT.Rows[0][txtControl.Name.Replace("txt", "")].ToString();
            }
            else if (control.GetType().ToString() == "DevExpress.XtraEditors.MemoEdit")
            {
                MemoEdit txtControl = (MemoEdit)control;
                txtControl.Text = temDT.Rows[0][txtControl.Name.Replace("txt", "")].ToString();
            }
            else if (control.GetType().ToString() == "DevExpress.XtraEditors.CheckEdit")
            {
                CheckEdit chkControl = (CheckEdit)control;
                chkControl.Checked = Util.ToBool(temDT.Rows[0][chkControl.Name.Replace("chk", "")]);
            }
            else if (control.GetType().ToString() == "DevExpress.XtraEditors.LookUpEdit")
            {
                LookUpEdit cboControl = (LookUpEdit)control;
                if (temDT.Columns[cboControl.Name.Replace("cbo", "")].DataType.ToString() == "System.Int32")
                    cboControl.EditValue = Util.ToInt(temDT.Rows[0][cboControl.Name.Replace("cbo", "")]);
                else
                {
                    cboControl.Text = temDT.Rows[0][cboControl.Name.Replace("cbo", "")].ToString();
                    cboControl.ClosePopup();
                }
            }
        }

        /// <summary>
        /// Hàm gán giá trị kiểu ngày tháng trong DB vào TextEdit, format kiểu MMyyyy
        /// </summary>
        /// <param name="temDT"></param>
        /// <param name="txtControl"></param>
        public static void SetValueMMyyyy(DataTable temDT, TextEdit txtControl)
        {
            if (Util.DATETIME.ToDate(temDT.Rows[0][txtControl.Name.Replace("txt", "")]) != null)
                txtControl.Text = Util.DATETIME.ToDate(temDT.Rows[0][txtControl.Name.Replace("txt", "")]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
            else
                txtControl.Text = "";
        }

        public static string GetMMyyyy(object inDate)
        {
            if (Util.DATETIME.ToDate(inDate) != null)
                return Util.DATETIME.ToDate(inDate).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
            else
                return "";
        }
        public static string GetddMMyyyy(object inDate)
        {
            if (Util.DATETIME.ToDate(inDate) != null)
                return Util.DATETIME.ToDate(inDate).Value.ToString(MyConst.FORMAT_DATETIME.ddMMyyyy);
            else
                return "";
        }


        /// <summary>
        /// Hàm gán giá trị kiểu ngày tháng trong DB vào TextEdit, format kiểu ddMMyyyy
        /// </summary>
        /// <param name="temDT"></param>
        /// <param name="txtControl"></param>
        public static void SetValueddMMyyyy(DataTable temDT, TextEdit txtControl)
        {
            if (Util.DATETIME.ToDate(temDT.Rows[0][txtControl.Name.Replace("txt", "")]) != null)
                txtControl.Text = Util.DATETIME.ToDate(temDT.Rows[0][txtControl.Name.Replace("txt", "")]).Value.ToString(MyConst.FORMAT_DATETIME.ddMMyyyy);
            else
                txtControl.Text = "";
        }

        public static DataRow GetHoSo(string Khoa, string DauMoiID)
        {
            string sql = "Select AutoID, DauMoiID, DauMoi_ID, Khoa, So_Hieu_SQ, So_Hieu_QN, HoTen_KS, BiDanh, Chuc_Vu, " +
                        "dbo.GetTuDienNCap('" + MyConst.TD.BoPhan.Ma + "',CB.Don_Vi_CT,CB.DauMoiID) As TenDon_Vi_CT, " +
                        "dbo.GetTuDien('" + MyConst.TD.Chuc.Ma + "',CB.Chuc_Vu,CB.DauMoiID) As TenChuc_Vu, " +
                        "CB.Ngay_Sinh,   " +
                        "Gioi_Tinh, Dan_Toc_T,  Ton_Giao_T, dbo.GetDauMoi(CB.DauMoiID) As TenDauMoi,  " +
                        "dbo.GetUser(CB.UserID, CB.DauMoi_ID) As CapNhatCuoi, CreateDate, UpdateDate  " +
                        ", Ho " +
                        ", Dem   " +
                        ", Ten   " +
                        ", Ten_Khac   " +
                        ", dbo.GetTuDien('" + MyConst.TD.Cap.Ma + "',CB.Cap_Bac,CB.DauMoiID)  As TenCap_Bac  " +
                        ", Thang_CBac " + //AsN'Tháng Cấp bậc' "+
                        ", Lan_NLuong " + //As N'Lần nâng lương' "+
                        ", HeSoLuong " + //As N'Hệ số lương' "+
                        ", Bac_Luong " + //As N'Bậc lương' "+
                        ", Muc_Luong " + //As N'Mức lương' "+ 
                        ",Thang_NL " + //As N'Tháng nâng lương' "+ 
                        ", Thang_CVu " + //As N'Tháng chức vụ' "+
                        ", Chuc_Kiem " + //As N'Chức kiêm'  "+
                        ", dbo.GetTuDien('" + MyConst.TD.Khoi_CT.Ma + "',CB.Khoi_CTac,CB.DauMoiID)  As TenKhoi_CT  " +
                        ", dbo.GetTuDien('" + MyConst.TD.BinhC.Ma + "',CB.Binh_Chung,CB.DauMoiID) As TenBinh_Chung  " +
                        ", dbo.GetTuDien('" + MyConst.TD.Ng_CNCT.Ma + "',CB.Nganh_CTac,CB.DauMoiID) As TenNganh_CTac " +
                        ", dbo.GetTuDien('" + MyConst.TD.LoaiCB.Ma + "',CB.Loai_CBo,CB.DauMoiID) As TenLoai_CBo  " +
                        ", dbo.GetTuDien('" + MyConst.TD.VanHoa.Ma + "',CB.Van_Hoa,CB.DauMoiID) As TenVan_Hoa  " +
                        ", CHQL " + //As N'Trình độ CHQL' "+
                        ", LLCT " + //As N'Trình độ LLCT' "+
                        ", CMNV " + //As N'Trình độ CMNV' "+
                        ", dbo.GetTuDien('" + MyConst.TD.HocHam.Ma + "',CB.HocHam,CB.DauMoiID) As TenHocHam  " +
                        ", dbo.GetTuDien('" + MyConst.TD.HocVi.Ma + "',CB.HocVi,CB.DauMoiID) As TenHocVi  " +
                        ", dbo.GetTuDien('" + MyConst.TD.Nguon.Ma + "',CB.Nguon_VDN,CB.DauMoiID) As TenNguon_VDN " +
                        ", Gia_Dinh_T " + //As N'TP Gia đình' "+
                        ", Ban_Than_T " + //As N'TP Bản thân' "+
                        ",Ngay_VDang " + //As N'Ngày vào đảng' "+
                        ", Ngay_CDCT " + //As N'Ngày vào đảng chính thức' "+
                        ", CVu_Dang_T " + //As N'Chức vụ đảng' "+
                        ", CVu_DThe_T " + //As N'Chức vụ đoàn thể' "+
                        ", Thang_Doan " + //As N'Tháng vào đoàn' "+
                        ", Thang_CM " + //As N'Tháng cách mạng' "+
                        ", Thang_BC " + //As N'Tháng biên chế' "+
                        ", Thang_NNgu " + //As N'Tháng nhập ngũ' "+
                        ", Thang_XNgu " + //As N'Tháng xuất ngũ' "+
                        ", Thang_TNgu " + //As N'Tháng tái ngũ' "+
                        ", dbo.GetTuDien('" + MyConst.TD.Que.Ma + "',CB.NQuan_HT,CB.DauMoiID) As TenNQuan_HT  " +
                        ", NQuan_XP " + //As N'Nguyên quán Xã Phường' "+
                        ", dbo.GetTuDien('" + MyConst.TD.Que.Ma + "',CB.TQuan_HT,CB.DauMoiID) As TenTQuan_HT  " +
                        ", TQuan_XP " + //As N'Trú quán Xã Phường' "+
                        ", Loai_No_T " + //As N'Loại nhà ở' "+
                        ", DTich_No " + //As N'Diện tích nhà ở' "+
                        ", Nguon_Go_T " + //As N'Nguồn gốc nhà ở' "+
                        ", Dia_Chi " + //As N'Địa chỉ' "+
                        ", dbo.GetTuDien('" + MyConst.TD.SucK.Ma + "',CB.Loai_SKhoe,CB.DauMoiID) As TenLoai_SKhoe   " +
                        ", Benh_Chi_T " + //As N'Bệnh chính' "+
                        ", Thuong_T_T " + //As N'Thương tật' "+
                        ", dbo.GetTuDien('" + MyConst.TD.SuDung.Ma + "',CB.Huong_SD,CB.DauMoiID) As TenHuong_SD  " +
                        ", dbo.GetTuDien('" + MyConst.TD.LDo_Tang.Ma + "',CB.Ly_Do_Vao,CB.DauMoiID) As TenLy_Do_Vao   " +
                        ", dbo.GetTuDien('" + MyConst.TD.LDo_Giam.Ma + "',CB.Ly_Do_Ra,CB.DauMoiID) As TenLy_Do_Ra    " +
                        ", Ngay_Vao " + //As N'Ngày vào' "+
                        ", Ngay_Ra " + //As N'Ngày ra'     "+
                        ", Deleted " + //As N'Đã xóa hay chưa' "+
                        ", ChuyenRa " + //As N'Đã chuyển ra hay chưa' "+    
                        ", '' As QT_CT, '' As QT_CD, '' As QT_DT " +
                        "From tblCanBo CB   " +
                        " Where Khoa='" + Khoa + "' And DauMoiID='" + DauMoiID + "' ";
            DataTable temDT = SqlHelper.ExecuteDataTable(CommandType.Text, sql);
            if (temDT.Rows.Count > 0)
            {
                #region QT_DT
                sql = "Select  " +
                        "dbo.GetTuDien('" + MyConst.TD.Truong.Ma + "',tblQT_DT.Ten_Co_So,tblCanBo.DauMoiID), " +
                        "dbo.GetTuDien('" + MyConst.TD.Ng_CNDT.Ma + "',tblQT_DT.CNganh_DT,tblCanBo.DauMoiID),  " +
                        "dbo.GetTuDien('" + MyConst.TD.CapHoc.Ma + "',tblQT_DT.Cap_Hoc,tblCanBo.DauMoiID),  " +
                        "tblQT_DT.Thang_BD, tblQT_DT.Thang_KT, IsNull( tblQT_DT.Loai_TN_T,''), IsNull(tblQT_DT.Bang_Cap,'') " +
                        "From  tblQT_DT, tblCanBo Where tblQT_DT.Deleted<>1 And tblCanBo.Khoa=tblQT_DT.Khoa And tblCanBo.Khoa='" + Khoa + "' And tblQT_DT.DauMoiID=tblCanBo.DauMoiID And tblCanBo.DauMoiID='" + DauMoiID + "' ";

                DataTable dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql);
                string sQT_DT = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        sQT_DT += " - " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        sQT_DT += " - " + dr[1].ToString();
                    if (dr[2] != null && dr[2].ToString().Trim() != "")
                        sQT_DT += " - " + dr[2].ToString();
                    if (Util.DATETIME.ToDate(dr[3]) != null)
                        sQT_DT += " - " + Util.DATETIME.ToDate(dr[3]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);

                    if (Util.DATETIME.ToDate(dr[4]) != null)
                        sQT_DT += " - " + Util.DATETIME.ToDate(dr[4]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (dr[5] != null && dr[5].ToString().Trim() != "")
                        sQT_DT += " - " + dr[5].ToString();
                    if (dr[6] != null && dr[6].ToString().Trim() != "")
                        sQT_DT += " - " + dr[6].ToString();

                    if (sQT_DT.ToString() != "")
                        sQT_DT += "; ";
                }
                if (sQT_DT != "") sQT_DT = sQT_DT.Substring(2);
                temDT.Rows[0]["QT_DT"] = sQT_DT;

                #endregion QT_DT

                #region QuaTrinhChienDau
                sql = "Select " +
                    "dbo.GetTuDien('" + MyConst.TD.ChienTr.Ma + "',CD.Ch_Truong,CB.DauMoiID), " +
                    "CD.Bat_Dau, Ket_Thuc, " +
                    "dbo.GetTuDien('" + MyConst.TD.CVCD.Ma + "',CD.CVi_ChDau,CB.DauMoiID), " +
                    "IsNull(CD.Don_Vi,'')  " +
                    "From tblQT_CD CD, tblCanBo CB Where CD.Deleted<>1 And CB.Khoa=CD.Khoa And CB.Khoa='" + Khoa + "'  And CD.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql);
                string sQT_CD = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        sQT_CD += " - " + dr[0].ToString();
                    if (Util.DATETIME.ToDate(dr[1]) != null)
                        sQT_CD += " - " + Util.DATETIME.ToDate(dr[1]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        sQT_CD += " - " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (dr[3] != null && dr[3].ToString().Trim() != "")
                        sQT_CD += " - " + dr[3].ToString();
                    if (dr[4] != null && dr[4].ToString().Trim() != "")
                        sQT_CD += " - " + dr[4].ToString();

                    if (sQT_CD.ToString() != "")
                        sQT_CD += "; ";
                }
                if (sQT_CD != "") sQT_CD = sQT_CD.Substring(2);

                temDT.Rows[0]["QT_CD"] = sQT_CD;

                #endregion QuaTrinhChienDau

                return temDT.Rows[0];
            }
            else
                return null;
        }

        public static void InHoSo(string Khoa, string DauMoiID)
        {
            try
            {
                CrystalDecisions.CrystalReports.Engine.ReportDocument objReport = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
                objReport.Load(System.Windows.Forms.Application.StartupPath + "\\Reports\\rptSoYeuLyLich.rpt");
                string sql = "";

                #region Set Data Source
                sql = "Select * From (SELECT " +
                            "tblCanBo.ngay_sinh As Ngay_Sinh_Date, " +
                            "dbo.GetDauMoi(tblCanBo.DauMoiID) As TenDauMoi, " +
                            "dbo.GetTuDien('" + MyConst.TD.TP.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenMa_Ban_Than_VC,  " +
                            "dbo.GetTuDien('" + MyConst.TD.TP.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenMa_Gia_Dinh_VC,  " +
                    // "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenQuan_He,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Cap.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenCap_Bac,  " +
                            "dbo.GetTuDien('" + MyConst.TD.NangLuong.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLan_NLuong,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Chuc.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenChuc_Vu,  " +
                            "dbo.GetTuDien('" + MyConst.TD.LoaiDV.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLoai_DV, " +
                            "dbo.GetTuDien('" + MyConst.TD.BoPhan.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenDon_Vi_CT, " +
                            "dbo.GetTuDien('" + MyConst.TD.Khoi_CT.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenKhoi_CTac, " +
                            "dbo.GetTuDien('" + MyConst.TD.BinhC.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenBinh_Chung,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Ng_CNCT.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenNganh_CTac,  " +
                            "dbo.GetTuDien('" + MyConst.TD.LoaiCB.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLoai_CBo, " +
                            "dbo.GetTuDien('" + MyConst.TD.VanHoa.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenVan_Hoa,  " +
                            "dbo.GetTuDien('" + MyConst.TD.HocHam.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenHocHam,  " +
                            "dbo.GetTuDien('" + MyConst.TD.HocVi.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenHocVi,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Nguon.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenNguon_VDN,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Que.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenNQuan_HT,  " +
                            "dbo.GetTuDien('" + MyConst.TD.Que.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenTQuan_HT,  " +
                            "dbo.GetTuDien('" + MyConst.TD.SucK.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLoai_SKhoe,  " +
                            "dbo.GetTuDien('" + MyConst.TD.SuDung.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenHuong_SD,  " +
                            "dbo.GetTuDien('" + MyConst.TD.LDo_Tang.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLy_Do_Vao,  " +
                            "dbo.GetTuDien('" + MyConst.TD.LDo_Giam.Ma + "',tblCanBo.Cap_Bac,tblCanBo.DauMoiID) As TenLy_Do_Ra,  " +
                            "dbo.GetDauMoi(tblCanBo.DauMoi_ID) As TenDauMoi_ID, " +
                            "Upper(tblCanBo.Ho) As HoUpper, Upper(tblCanBo.Dem) As DemUpper, Upper(tblCanBo.Ten) As TenUpper, " +
                            "tblCanBo.*,  " +
                            "tblTrichNgang.Ngay_HH, tblTrichNgang.Ngay_HV, tblTrichNgang.Di_NN, tblTrichNgang.Truoc_CM, tblTrichNgang.KTCT_BT, tblTrichNgang.KTCT_VC, tblTrichNgang.Nhan_Xet, tblTrichNgang.So_ACE, tblTrichNgang.So_AET " +
                            "  , tblTrichNgang.So_ACE_VC, tblTrichNgang.So_AET_VC, tblTrichNgang.Thu_May, tblTrichNgang.Thu_May_VC, tblTrichNgang.Ma_Gia_Dinh_VC, tblTrichNgang.Ma_Ban_Than_VC " +
                            "  , tblTrichNgang.Que_Quan_XPVC, tblTrichNgang.Tru_Quan_XPVC, tblTrichNgang.Que_Quan_HTVC, tblTrichNgang.Tru_Quan_HTVC, tblTrichNgang.Ghi_Them " +
                            "  , tblTrichNgang.Ngay_Trich, tblTrichNgang.Can_Cu_Trich " +
                            "FROM tblCanBo LEFT JOIN " +
                            "tblTrichNgang ON tblCanBo.Khoa = tblTrichNgang.Khoa " +
                            "Where tblCanBo.Khoa=@Khoa And tblCanBo.DauMoiID='" + DauMoiID + "') As CanBoAll ; ";
                List<SqlParameter> arrParameters = new List<SqlParameter>();
                arrParameters.Add(SqlHelper.CreateParameterObject("@Khoa", SqlDbType.NVarChar, ParameterDirection.Input, Khoa));



                sql += "Select * From (Select * From tblQT_CT Where Khoa=@Khoa And DauMoiID='" + DauMoiID + "') As CanBo_QT_CT ;";
                sql += "Select * From(Select " +
                       "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                       "TN.Ten_TNhan, TN.Nam_Sinh, TN.Nghe " +
                       "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And TN.Quan_He In " +
                       "( " +
                       "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.QuanHe.Ma + "' And (TD.CapTren Like '%;13;%' Or TD.CapTren Like '%;14;%' Or TD.CapTren Like '%;15;%' Or TD.CapTren Like '%;16;%' Or TD.CapTren Like '%;17;%' Or TD.CapTren Like '%;18;%' Or TD.CapTren Like '%;19;%' )" +
                       ")) AS CanBo_CacCon";



                SqlConnection conn = new SqlConnection(SqlHelper.connectionString);
                SqlCommand cmd = new SqlCommand(sql, conn);

                for (int i = 0; i < arrParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(arrParameters[i].ParameterName, arrParameters[i].Value);
                }

                DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, sql, arrParameters);
                ds.Tables[0].TableName = "CanBoAll";
                ds.Tables[1].TableName = "CanBo_QT_CT";
                ds.Tables[2].TableName = "CanBo_CacCon";

                objReport.SetDataSource(ds.Tables[0]);
                objReport.Subreports["rptCacCon"].SetDataSource(ds.Tables[2]);//cac con
                objReport.Subreports["rptCanBo_QT_CT"].SetDataSource(ds.Tables[1]);// cong tac
                frmReport.Instance.crvMain.ReportSource = objReport;

                #endregion Set Data Source

                #region Add param to Report

                CrystalDecisions.Shared.ParameterDiscreteValue crParaDisValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
                CrystalDecisions.Shared.ParameterValues crParaValue = new CrystalDecisions.Shared.ParameterValues();

                #region Ngoai ngu
                sql = "select  NgonNgu_T, TrinhDo, ThoiGian From tblTieng Where Deleted<>1 And Loai=" + MyConst.LoaiTieng.NgoaiNgu + " And Khoa=@Khoa And DauMoiID='" + DauMoiID + "' ";
                DataTable dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += "- " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += "- " + dr[1].ToString();
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += "- " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["NgoaiNgu"].ApplyCurrentValues(crParaValue);
                #endregion Ngoai ngu

                #region tieng dan toc
                sql = "select  NgonNgu_T, TrinhDo, ThoiGian From tblTieng Where Deleted<>1 And Loai=" + MyConst.LoaiTieng.TiengDanToc + " And Khoa=@Khoa And DauMoiID='" + DauMoiID + "' ";
                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += "- " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += "- " + dr[1].ToString();
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += "- " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TiengDanToc"].ApplyCurrentValues(crParaValue);
                #endregion TiengDanToc

                #region QT_DT
                sql = "Select  " +
                        "dbo.GetTuDien('" + MyConst.TD.Truong.Ma + "',tblQT_DT.Ten_Co_So,tblCanBo.DauMoiID), " +
                        "dbo.GetTuDien('" + MyConst.TD.Ng_CNDT.Ma + "',tblQT_DT.CNganh_DT,tblCanBo.DauMoiID),  " +
                        "dbo.GetTuDien('" + MyConst.TD.CapHoc.Ma + "',tblQT_DT.Cap_Hoc,tblCanBo.DauMoiID),  " +
                        "tblQT_DT.Thang_BD, tblQT_DT.Thang_KT, IsNull( tblQT_DT.Loai_TN_T,''), IsNull(tblQT_DT.Bang_Cap,'') " +
                        "From  tblQT_DT, tblCanBo Where tblQT_DT.Deleted<>1 And tblCanBo.Khoa=tblQT_DT.Khoa And tblCanBo.Khoa=@Khoa And tblQT_DT.DauMoiID=tblCanBo.DauMoiID And tblCanBo.DauMoiID='" + DauMoiID + "' ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[1].ToString();
                    if (dr[2] != null && dr[2].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[2].ToString();
                    if (Util.DATETIME.ToDate(dr[3]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[3]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);

                    if (Util.DATETIME.ToDate(dr[4]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[4]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);

                    if (dr[5] != null && dr[5].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[5].ToString();
                    if (dr[6] != null && dr[6].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[6].ToString();

                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["QuaTrinhDaoTao"].ApplyCurrentValues(crParaValue);
                #endregion QT_DT

                #region Khen thuong
                sql = "Select " +
                        "IsNull(DG.NDung_SK,''), IsNull(DG.Ghi_Chu,''),  DG.Thang_SK " +
                        "From  tblQT_DG DG, tblCanBo CB Where DG.Deleted<>1 And CB.Khoa=DG.Khoa And DG.Khoa=@Khoa  And DG.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And DG.SKien_DGia In  " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.SK_DGia.Ma + "' And TD.CapTren like '%;1;%' " +
                        ") ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[1].ToString();
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["KhenThuong"].ApplyCurrentValues(crParaValue);
                #endregion Khen thuong

                #region Danh hiệu cao quý
                sql = "Select " +
                        "IsNull(DG.NDung_SK,''),IsNull(DG.Ghi_Chu,'') , DG.Thang_SK " +
                        "From  tblQT_DG DG, tblCanBo CB Where DG.Deleted<>1 And CB.Khoa=DG.Khoa And DG.Khoa=@Khoa  And DG.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And DG.SKien_DGia In  " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.SK_DGia.Ma + "' And TD.CapTren like '%;62;%' " +
                        ") ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[1].ToString();
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["DanhHieu"].ApplyCurrentValues(crParaValue);
                #endregion Danh hiệu cao quý

                #region KyLuat
                sql = "Select " +
                       "IsNull(DG.NDung_SK,''),IsNull(DG.Ghi_Chu,'') , DG.Thang_SK " +
                        "From  tblQT_DG DG, tblCanBo CB Where DG.Deleted<>1 And CB.Khoa=DG.Khoa And DG.Khoa=@Khoa  And DG.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And DG.SKien_DGia In  " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.SK_DGia.Ma + "' And TD.CapTren like '%;38;%' " +
                        ") ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[0].ToString();
                    if (dr[1] != null && dr[1].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[1].ToString();
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["KyLuat"].ApplyCurrentValues(crParaValue);
                #endregion KyLuat

                #region QuaTrinhChienDau
                sql = "Select " +
                    "dbo.GetTuDien('" + MyConst.TD.ChienTr.Ma + "',CD.Ch_Truong,CB.DauMoiID), " +
                    "CD.Bat_Dau, Ket_Thuc, " +
                    "dbo.GetTuDien('" + MyConst.TD.CVCD.Ma + "',CD.CVi_ChDau,CB.DauMoiID), " +
                    "IsNull(CD.Don_Vi,'')  " +
                    "From tblQT_CD CD, tblCanBo CB Where CD.Deleted<>1 And CB.Khoa=CD.Khoa And CB.Khoa=@Khoa  And CD.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' ";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);
                crParaDisValue.Value = "";
                foreach (DataRow dr in dtParam.Rows)
                {
                    if (dr[0] != null && dr[0].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[0].ToString();
                    if (Util.DATETIME.ToDate(dr[1]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[1]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (Util.DATETIME.ToDate(dr[2]) != null)
                        crParaDisValue.Value += ", " + Util.DATETIME.ToDate(dr[2]).Value.ToString(MyConst.FORMAT_DATETIME.MMyyyy);
                    if (dr[3] != null && dr[3].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[3].ToString();
                    if (dr[4] != null && dr[4].ToString().Trim() != "")
                        crParaDisValue.Value += ", " + dr[4].ToString();

                    if (crParaDisValue.Value.ToString() != "")
                        crParaDisValue.Value += "; ";
                }
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["QuaTrinhChienDau"].ApplyCurrentValues(crParaValue);
                #endregion QuaTrinhChienDau

                #region Cha
                sql = "Select " +
                        "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                        "TN.* " +
                        "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa  And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And TN.Quan_He In " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.QuanHe.Ma + "' And TD.CapTren like '%;5;%'  " +
                        ")";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);

                string ChaHoTen = "", ChaNamSinh = "", ChaNgheNghiep = "", ChaChoO = "";
                if (dtParam.Rows.Count > 0)
                {
                    try
                    {
                        ChaHoTen = dtParam.Rows[0]["Ten_TNhan"].ToString();
                    }
                    catch { }
                    try
                    {
                        ChaNamSinh = dtParam.Rows[0]["Nam_Sinh"].ToString();
                    }
                    catch { }
                    try
                    {
                        ChaNgheNghiep = dtParam.Rows[0]["Nghe"].ToString();
                    }
                    catch { }
                    try
                    {
                        ChaChoO = dtParam.Rows[0]["Dia_Chi"].ToString();
                    }
                    catch { }
                }
                crParaDisValue.Value = ChaHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = ChaNamSinh;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaNamSinh"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = ChaNgheNghiep;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaNgheNghiep"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = ChaChoO;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaChoO"].ApplyCurrentValues(crParaValue);


                #endregion Cha

                #region Me
                sql = "Select " +
                        "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                        "TN.* " +
                        "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa  And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And TN.Quan_He In " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.QuanHe.Ma + "' And TD.CapTren like '%;6;%'  " +
                        ")";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);

                string MeHoTen = "", MeNamSinh = "", MeNgheNghiep = "", MeChoO = "";
                if (dtParam.Rows.Count > 0)
                {
                    try
                    {
                        MeHoTen = dtParam.Rows[0]["Ten_TNhan"].ToString();
                    }
                    catch { }
                    try
                    {
                        MeNamSinh = dtParam.Rows[0]["Nam_Sinh"].ToString();
                    }
                    catch { }
                    try
                    {
                        MeNgheNghiep = dtParam.Rows[0]["Nghe"].ToString();
                    }
                    catch { }
                    try
                    {
                        MeChoO = dtParam.Rows[0]["Dia_Chi"].ToString();
                    }
                    catch { }
                }
                crParaDisValue.Value = MeHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = MeNamSinh;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeNamSinh"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = MeNgheNghiep;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeNgheNghiep"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = MeChoO;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeNoiO"].ApplyCurrentValues(crParaValue);


                #endregion Me

                #region ChaVC
                sql = "Select " +
                        "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                        "TN.* " +
                        "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa  And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And TN.Quan_He In " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.QuanHe.Ma + "' And TD.CapTren like '%;5;%'  " +
                        ")";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);

                string ChaVCHoTen = "", ChaVCNamSinh = "", ChaVCNgheNghiep = "", ChaVCChoO = "";
                if (dtParam.Rows.Count > 0)
                {
                    try
                    {
                        ChaVCHoTen = dtParam.Rows[0]["Ten_TNhan"].ToString();
                    }
                    catch { }
                    try
                    {
                        ChaVCNamSinh = dtParam.Rows[0]["Nam_Sinh"].ToString();
                    }
                    catch { }
                    try
                    {
                        ChaVCNgheNghiep = dtParam.Rows[0]["Nghe"].ToString();
                    }
                    catch { }

                }
                crParaDisValue.Value = ChaVCHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaVCHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = ChaVCNamSinh;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaVCNamSinh"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = ChaVCNgheNghiep;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["ChaVCNgheNghiep"].ApplyCurrentValues(crParaValue);



                #endregion ChaVC

                #region MeVC
                sql = "Select " +
                        "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                        "TN.* " +
                        "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "'  And TN.Quan_He In " +
                        "( " +
                        "Select TuDienID From tblTuDien TD Where CB.DauMoiID=CB.DauMoiID And MaDanhMuc='" + MyConst.TD.QuanHe.Ma + "' And TD.CapTren like '%;6;%'  " +
                        ")";

                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);

                string MeVCHoTen = "", MeVCNamSinh = "", MeVCNgheNghiep = "", MeVCChoO = "";
                if (dtParam.Rows.Count > 0)
                {
                    try
                    {
                        MeVCHoTen = dtParam.Rows[0]["Ten_TNhan"].ToString();
                    }
                    catch { }
                    try
                    {
                        MeVCNamSinh = dtParam.Rows[0]["Nam_Sinh"].ToString();
                    }
                    catch { }
                    try
                    {
                        MeVCNgheNghiep = dtParam.Rows[0]["Nghe"].ToString();
                    }
                    catch { }

                }
                crParaDisValue.Value = MeVCHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeVCHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = MeVCNamSinh;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeVCNamSinh"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = MeVCNgheNghiep;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["MeVCNgheNghiep"].ApplyCurrentValues(crParaValue);



                #endregion MeVC

                #region VC
                sql = "Select " +
                        "dbo.GetTuDien('" + MyConst.TD.QuanHe.Ma + "',TN.Quan_He,CB.DauMoiID) As TenQuan_He, " +
                        "TN.* " +
                        "From tblThanNhan TN, tblCanBo CB Where TN.Deleted<>1 And CB.Khoa=TN.Khoa And CB.Khoa=@Khoa  And TN.DauMoiID=CB.DauMoiID And CB.DauMoiID='" + DauMoiID + "' And (TN.Quan_He=11 Or TN.Quan_He=12) ";


                dtParam = SqlHelper.ExecuteDataTable(CommandType.Text, sql, arrParameters);

                string VCHoTen = "", VCNamSinh = "", VCNgheNghiep = "", VCChoO = "";
                if (dtParam.Rows.Count > 0)
                {
                    try
                    {
                        VCHoTen = dtParam.Rows[0]["Ten_TNhan"].ToString();
                    }
                    catch { }
                    try
                    {
                        VCNamSinh = dtParam.Rows[0]["Nam_Sinh"].ToString();
                    }
                    catch { }
                    try
                    {
                        VCNgheNghiep = dtParam.Rows[0]["Nghe"].ToString();
                    }
                    catch { }
                    try
                    {
                        VCChoO = dtParam.Rows[0]["Dia_Chi"].ToString();
                    }
                    catch { }

                }
                crParaDisValue.Value = VCHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["VCHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = VCNamSinh;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["VCNamSinh"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = VCNgheNghiep;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["VCNgheNghiep"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = VCChoO;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["VCChoO"].ApplyCurrentValues(crParaValue);


                #endregion VC

                #region TrichLuc

                frmCanBoPrint ff = new frmCanBoPrint();
                string TrichLucHoTen = "", TrichLucNgayTrich = "", TrichLucChucVu = "", TrichLucCapBac = "", TrichLucCanCuTrich = "";
                if (ff.ShowDialog() == DialogResult.OK)
                {
                    TrichLucNgayTrich = ff.txtNgayTrich.Text.Trim();
                    TrichLucHoTen = ff.txtNguoiTrich_HoTen.Text.Trim();
                    TrichLucChucVu = ff.txtNguoiTrich_ChucVu.Text.Trim();
                    TrichLucCapBac = ff.txtCapBac.Text.Trim();
                    TrichLucCanCuTrich = ff.txtCanCuTrich.Text.Trim();
                }

                DataTable trichlucDT = SqlHelper.ExecuteDataTable(CommandType.Text, "Select * From tblCanBoPrint");

                crParaDisValue.Value = TrichLucHoTen;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TrichLucHoTen"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = TrichLucChucVu;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TrichLucChucVu"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = TrichLucCapBac;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TrichLucCapBac"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = TrichLucNgayTrich;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TrichLucNgayTrich"].ApplyCurrentValues(crParaValue);

                crParaDisValue.Value = TrichLucCanCuTrich;
                crParaValue.Clear();
                crParaValue.Add(crParaDisValue);
                objReport.DataDefinition.ParameterFields["TrichLucCanCuTrich"].ApplyCurrentValues(crParaValue);


                #endregion TrichLuc

                #endregion Add param to Report

                frmReport.Instance.ShowDialog(); objReport.Dispose(); System.GC.Collect();
            }
            catch (Exception _ex)
            {
                ShowMessage(_ex);
            }
        }

        public class DBHelper
        {
            #region GetDbType

            /// <summary>
            /// Maps a system type to a DbType enumeration.
            /// <para>Used at BaseBO, Database, Transaction</para>
            /// </summary>
            /// <param name="sysType">The system type to translate</param>
            /// <returns>A DbType enumeration</returns>
            public static DbType GetDbType(System.Type sysType)
            {
                switch (sysType.Name.ToLower())
                {
                    case "guid":
                        return DbType.Guid;
                    case "string":
                        return DbType.String;
                    case "integer":
                    case "int32":
                    case "int":
                        return DbType.Int32;
                    case "decimal":
                        return DbType.Decimal;
                    case "byte[]":
                        return DbType.Binary;
                    case "object":
                        return DbType.Object;
                    case "byte":
                        return DbType.Byte;
                    case "bool":
                    case "boolean":
                        return DbType.Boolean;
                    case "datetime":
                        return DbType.DateTime;
                    case "timespan":
                        return DbType.Time;
                    case "double":
                        return DbType.Double;
                    case "short":
                    case "int16":
                        return DbType.Int16;
                    case "long":
                    case "int64":
                        return DbType.Int64;
                    case "float":
                        return DbType.Single;
                    case "single":
                        return DbType.Single;

                    //TODO: 01 Dec 08: SonP: verify this
                    case "dbnull":
                        return DbType.DateTime;

                    default:
                        throw new ArgumentException("The parameter type is unknown");
                    //return DbType.Object;
                }
            }

            #endregion GetDbType

            #region GetParametter

            /// <summary>
            /// Build the param name base on the provided column name (concat @ if missing)
            /// </summary>
            /// <param name="columnName">Name of column</param>
            /// <returns>Name of parameter</returns>
            public static string GetParamName(string columnName)
            {
                if (!columnName.StartsWith("@"))
                    return "@" + columnName;
                else
                    return columnName;
            }

            #endregion GetParametter

            #region GetColumnName

            /// <summary>
            /// Return the column name base on the provided name (remove @ if any)
            /// </summary>
            /// <param name="columnName">Provided name</param>
            /// <returns>Correct Column name</returns>
            public static string GetColumnName(string columnName)
            {
                if (columnName.StartsWith("@"))
                    return columnName.Replace("@", string.Empty);
                else
                    return columnName;
            }

            #endregion GetColumnName

            #region SetDataTableName

            /// <summary>
            /// Sets the name of the data table.
            /// </summary>
            /// <param name="dt">The dt</param>
            /// <param name="name">The name</param>
            public static void SetDataTableName(DataTable dt, string name)
            {
                if (dt == null)
                    return;

                dt.TableName = name;
            }

            #endregion SetDataTableName

            public static void AddTableToDataSet(DataSet ds, DataTable dt)
            {
                if (dt.DataSet != null)
                {
                    dt.DataSet.Tables.Remove(dt);
                }

                ds.Tables.Add(dt);
            }
        }
        /// <summary>
        /// Các Hàm kiểm tra xem chuỗi truyền vào có đúng là kiểu Tháng/Năm, ngày/tháng/năm  hay không
        /// </summary>
        public class MyDateTime
        {
            /// <summary>
            ///Hàm kiểm tra xem chuỗi truyền vào có đúng là kiểu Tháng/Năm hay không (vd: 01/2013).  
            /// </summary>
            /// <param name="thangnam"></param>
            /// <returns></returns>
            public static bool IsMonthYear(string sMonthYear)
            {
                string[] arr = sMonthYear.Split('/');
                if (arr.Length != 2)
                    return false;
                else
                    if (Util.ToInt(arr[0]) <= 0 || Util.ToInt(arr[0]) > 12 || Util.ToInt(arr[1]) <= DateTime.MinValue.Year)
                        return false;
                return true;
            }
            /// <summary>
            /// Hàm kiểm tra xem chuỗi truyền vào dạng ngày/tháng/năm
            /// </summary>
            /// <param name="ngaythangnam"></param>
            /// <returns></returns>
            public static bool IsDayMonthYear(string sDayMonthYear)
            {
                try
                {
                    string[] arr = sDayMonthYear.Split('/');
                    if (arr.Length != 3)
                        return false;
                    else
                    {
                        DateTime myDate = new DateTime(Util.ToInt(arr[2]), Util.ToInt(arr[1]), Util.ToInt(arr[0]));

                    }
                    return true;
                }
                catch
                {
                    return false;
                }
            }

            /// <summary>
            /// Hàm chuyển chuỗi dạng ngày tháng năm hoặc tháng năm về dạng YYYYMMDD
            /// Nếu là tháng năm thì ngày sẽ là 1
            /// Nếu là năm sẽ cho tháng =1, ngày = 1
            /// Hàm sẽ tự động kiểm tra nếu sai định dạng ngày tháng thì kết quả trả về là chuỗi rỗng
            /// </summary>
            /// <param name="ngaythangnam"></param>
            /// <returns></returns>
            public static string ToYYYYMMDD(string sDayMonthYear)
            {
                try
                {
                    string rValue = "";

                    string[] arrS = sDayMonthYear.Split('/');
                    if (arrS.Length == 2)
                        return (new DateTime(Util.ToInt(arrS[1]), Util.ToInt(arrS[0]), 1)).ToString("yyyy/MM/dd");
                    else if (arrS.Length == 3)
                        return (new DateTime(Util.ToInt(arrS[2]), Util.ToInt(arrS[1]), Util.ToInt(arrS[0]))).ToString("yyyy/MM/dd");
                    else if (arrS.Length == 1)
                        return (new DateTime(Util.ToInt(arrS[0]), 1, 1)).ToString("yyyy/MM/dd");

                    return rValue;
                }
                catch
                {
                    return "";
                }
            }
            /// <summary>
            /// Hàm trả về ngày tháng từ yyyy/MM/dd
            /// </summary>
            /// <param name="sYMD"></param>
            /// <returns></returns>
            public static Nullable<DateTime> ToDate_YMD(string sYMD)
            {
                try
                {
                    string[] arrS = sYMD.Split('/');

                    return new DateTime(Util.ToInt(arrS[0]), Util.ToInt(arrS[1]), Util.ToInt(arrS[2]));

                }
                catch
                {
                    return null;
                }
            }
        }





    }
    class INPUTBOX
    {
        /// <summary>
        /// Hàm trả về null nếu ấn nút bỏ qua.
        /// </summary>
        /// <param name="TieuDe"></param>
        /// <param name="LoiNhac"></param>
        /// <param name="GiaTriMacDinh"></param>
        /// <returns></returns>
        public static string Input(string TieuDe, string LoiNhac, string GiaTriMacDinh)
        {
            frmInputBox ff = new frmInputBox();
            ff.Text = TieuDe;
            ff.lblLoiNhac.Text = LoiNhac;
            ff.txtKetQua.Text = GiaTriMacDinh;

            if (ff.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                return ff.txtKetQua.Text.Trim();
            else
                return null;

        }
        public static string Input(string TieuDe, string LoiNhac)
        {
            return Input(TieuDe, LoiNhac, string.Empty);
        }
    }



}
